package com.eff.android.lib.asynctask;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

//DatabaseHelper.java
public class DatabaseHelper extends SQLiteOpenHelper {
 
    // Logcat tag
    private static final String LOG = "DatabaseHelper";
 
    // Database Version
    private static final int DATABASE_VERSION = 1;
 
    // Database Name
    private static final String DATABASE_NAME = "db_EnglishFun";
 
    // Table Names
    private static final String TABLE = "tb_Word";
 
    // Column names
    private static final String KEY_ID_HINH = "id_hinh";
    private static final String KEY_VAN_BAN = "van_ban";
    private static final String KEY_Y_NGHIA = "y_nghia";
    private static final String KEY_URI_HINH = "uri_hinh";
    private static final String KEY_PHIEN_AM_QUOC_TE = "phien_am_quoc_te";
    private static final String KEY_LOAI_HINH = "loai_hinh";
    private static final String KEY_MA_CHU_DE = "ma_chu_de";
    private static final String KEY_DATE_CREATE = "date_create";
 
    // Table Create Statements
    // Table create statement
    private static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS "
            + TABLE + "(" + KEY_ID_HINH + " TEXT PRIMARY KEY," + KEY_VAN_BAN
            + " TEXT," + KEY_Y_NGHIA + " TEXT," + KEY_URI_HINH
            + " TEXT," + KEY_PHIEN_AM_QUOC_TE + " TEXT," + KEY_LOAI_HINH
            + " TEXT," + KEY_MA_CHU_DE + " TEXT," + KEY_DATE_CREATE
            + " TEXT" + ")";
 
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
 
        // creating required tables
        db.execSQL(CREATE_TABLE);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + TABLE);
 
        // create new tables
        onCreate(db);
    }
    
    public Boolean CheckExistModel(String model_id) {
		
    	SQLiteDatabase db = this.getReadableDatabase();
	     
        String selectQuery = "SELECT  * FROM " + TABLE + " WHERE "
                + KEY_ID_HINH + " = " + model_id;
        
        Log.e(LOG, selectQuery);
     
        Cursor c = db.rawQuery(selectQuery, null);
     
        if (c != null)
        	if (c.moveToFirst())
        		return true;
        
        return false;
	}
    
    /*
     * Creating a model
     */
    public long createModel(Model model) {
    	  
    	//Log.d("Ket qua tim : ", CheckExistModel(model.getIdHinh()).toString());
    	
    	if (!CheckExistModel(model.getIdHinh()))
    	{
	        SQLiteDatabase db = this.getWritableDatabase();
	     
	        ContentValues values = new ContentValues();
	        values.put(KEY_ID_HINH, model.getIdHinh());
	        values.put(KEY_VAN_BAN, model.getVanBan());
	        values.put(KEY_Y_NGHIA, model.getYNghia());
	        values.put(KEY_URI_HINH, model.getUriHinh());
	        values.put(KEY_PHIEN_AM_QUOC_TE, model.getPhienAmQuocTe());
	        values.put(KEY_LOAI_HINH, model.getLoaiHinh());
	        values.put(KEY_MA_CHU_DE, model.getMaChuDe());
	        values.put(KEY_DATE_CREATE, model.getDateCreate());
	     
	        // insert row
	        return db.insert(TABLE, null, values);
	     
    	}
    	
    	return -1;
    }
    
    /*
     * get single model
     */
    public Model getModel(String model_id) {
    	

    	Log.d("Ket qua tim : ", CheckExistModel(model_id).toString());

    	
    	if (CheckExistModel(model_id))
    	{
	        SQLiteDatabase db = this.getReadableDatabase();
	     
	        String selectQuery = "SELECT  * FROM " + TABLE + " WHERE "
	                + KEY_ID_HINH + " = " + model_id;
	        
	        Log.e(LOG, selectQuery);
	     
	        Cursor c = db.rawQuery(selectQuery, null);
	     
	        if (c != null)
	            c.moveToFirst();
	     
	        Model md = new Model();
	        md.setIdHinh(c.getString(c.getColumnIndex(KEY_ID_HINH)));
	        md.setVanBan(c.getString(c.getColumnIndex(KEY_VAN_BAN)));
	        md.setYNghia(c.getString(c.getColumnIndex(KEY_Y_NGHIA)));
	        md.setUriHinh(c.getString(c.getColumnIndex(KEY_URI_HINH)));
	        md.setPhienAmQuocTe(c.getString(c.getColumnIndex(KEY_PHIEN_AM_QUOC_TE)));
	        md.setLoaiHinh(c.getString(c.getColumnIndex(KEY_LOAI_HINH)));
	        md.setMaChuDe(c.getString(c.getColumnIndex(KEY_MA_CHU_DE)));
	        md.setDateCreate(c.getString(c.getColumnIndex(KEY_DATE_CREATE)));
	     
	        return md;
    	}
    	return new Model("0", "0", "0", "0", "0", "0", "0", "0");
    }
    
    /*
     * getting all models
     * */
    public List<Model> getAllModels(int n) {
    	
        List<Model> models = new ArrayList<Model>();
        String selectQuery = "SELECT  * FROM " + TABLE + " ORDER BY " + KEY_ID_HINH + " ASC LIMIT " + n;
     
        Log.e(LOG, selectQuery);
     
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
     
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
            	Model md = new Model();
                md.setIdHinh(c.getString(c.getColumnIndex(KEY_ID_HINH)));
                md.setVanBan(c.getString(c.getColumnIndex(KEY_VAN_BAN)));
                md.setYNghia(c.getString(c.getColumnIndex(KEY_Y_NGHIA)));
                md.setUriHinh(c.getString(c.getColumnIndex(KEY_URI_HINH)));
                md.setPhienAmQuocTe(c.getString(c.getColumnIndex(KEY_PHIEN_AM_QUOC_TE)));
                md.setLoaiHinh(c.getString(c.getColumnIndex(KEY_LOAI_HINH)));
                md.setMaChuDe(c.getString(c.getColumnIndex(KEY_MA_CHU_DE)));
                md.setDateCreate(c.getString(c.getColumnIndex(KEY_DATE_CREATE)));
     
                // adding to model list
                models.add(md);
            } while (c.moveToNext());
            
            return models;
        }
     
        List<Model> result = new ArrayList<Model>();
        return result;
    }
    
    /*
     * getting all models under single KEY_MA_CHU_DE
     * */
    public List<Model> getAllModelsByTag(String tag_name) {
    	
        List<Model> listModels = new ArrayList<Model>();
     
        String selectQuery = "SELECT  * FROM " + TABLE + " WHERE "
                + KEY_MA_CHU_DE + " = " + tag_name;
     
        Log.e(LOG, selectQuery);
     
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
     
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
            	Model md = new Model();
                md.setIdHinh(c.getString(c.getColumnIndex(KEY_ID_HINH)));
                md.setVanBan(c.getString(c.getColumnIndex(KEY_VAN_BAN)));
                md.setYNghia(c.getString(c.getColumnIndex(KEY_Y_NGHIA)));
                md.setUriHinh(c.getString(c.getColumnIndex(KEY_URI_HINH)));
                md.setPhienAmQuocTe(c.getString(c.getColumnIndex(KEY_PHIEN_AM_QUOC_TE)));
                md.setLoaiHinh(c.getString(c.getColumnIndex(KEY_LOAI_HINH)));
                md.setMaChuDe(c.getString(c.getColumnIndex(KEY_MA_CHU_DE)));
                md.setDateCreate(c.getString(c.getColumnIndex(KEY_DATE_CREATE)));
     
                // adding to model list
                listModels.add(md);
            } while (c.moveToNext());
            
            return listModels;
        }
     
        List<Model> result = new ArrayList<Model>();
        return result;
    }
    
    /**
     * getting model count
     */
    public int getModelCount() {

        String countQuery = "SELECT  * FROM " + TABLE;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
 
        int count = cursor.getCount();
        cursor.close();
 
        // return count
        return count;
    }
    
    /*
     * Updating a model
     */
    public long updateModel(Model model) {
    	
    	if (!CheckExistModel(model.getIdHinh()))
    	{
	        SQLiteDatabase db = this.getWritableDatabase();
	     
	        ContentValues values = new ContentValues();
	        values.put(KEY_ID_HINH, model.getIdHinh());
	        values.put(KEY_VAN_BAN, model.getVanBan());
	        values.put(KEY_Y_NGHIA, model.getYNghia());
	        values.put(KEY_URI_HINH, model.getUriHinh());
	        values.put(KEY_PHIEN_AM_QUOC_TE, model.getPhienAmQuocTe());
	        values.put(KEY_LOAI_HINH, model.getLoaiHinh());
	        values.put(KEY_MA_CHU_DE, model.getMaChuDe());
	        values.put(KEY_DATE_CREATE, model.getDateCreate());
	        
	        // updating row
	        return db.update(TABLE, values, KEY_ID_HINH + " = ?",
	                new String[] { String.valueOf(model.getIdHinh()) });
    	}
    	return -1;
    }
    
    /*
     * Deleting a todo
     */
    public void deleteModel(String model_id) {
    	
    	SQLiteDatabase db = this.getWritableDatabase();
    	db.delete(TABLE, KEY_ID_HINH + " = ?",
    			new String[] { String.valueOf(model_id) });
    }
    
    // closing database
    public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }
    
    /**
     * get datetime
     * */
    public String getDateTime() {
        SimpleDateFormat dateFormat = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss", Locale.getDefault());
        Date date = new Date();
        return dateFormat.format(date);
    }
}
